



* 1. Import CSV
import delimited using "${raw}/partisanship/Partisan_Balance_For_Use2011_06_09b.csv", clear

* 2. Keep only years between 1978 and 2010
keep if year >= 1978 & year <= 2010

* 3. Add state codes
gen state_code = ""

replace state_code = "AL" if state == "Alabama"
replace state_code = "AK" if state == "Alaska"
replace state_code = "AZ" if state == "Arizona"
replace state_code = "AR" if state == "Arkansas"
replace state_code = "CA" if state == "California"
replace state_code = "CO" if state == "Colorado"
replace state_code = "CT" if state == "Connecticut"
replace state_code = "DE" if state == "Delaware"
replace state_code = "FL" if state == "Florida"
replace state_code = "GA" if state == "Georgia"
replace state_code = "HI" if state == "Hawaii"
replace state_code = "ID" if state == "Idaho"
replace state_code = "IL" if state == "Illinois"
replace state_code = "IN" if state == "Indiana"
replace state_code = "IA" if state == "Iowa"
replace state_code = "KS" if state == "Kansas"
replace state_code = "KY" if state == "Kentucky"
replace state_code = "LA" if state == "Louisiana"
replace state_code = "ME" if state == "Maine"
replace state_code = "MD" if state == "Maryland"
replace state_code = "MA" if state == "Massachusetts"
replace state_code = "MI" if state == "Michigan"
replace state_code = "MN" if state == "Minnesota"
replace state_code = "MS" if state == "Mississippi"
replace state_code = "MO" if state == "Missouri"
replace state_code = "MT" if state == "Montana"
replace state_code = "NE" if state == "Nebraska"
replace state_code = "NV" if state == "Nevada"
replace state_code = "NH" if state == "New Hampshire"
replace state_code = "NJ" if state == "New Jersey"
replace state_code = "NM" if state == "New Mexico"
replace state_code = "NY" if state == "New York"
replace state_code = "NC" if state == "North Carolina"
replace state_code = "ND" if state == "North Dakota"
replace state_code = "OH" if state == "Ohio"
replace state_code = "OK" if state == "Oklahoma"
replace state_code = "OR" if state == "Oregon"
replace state_code = "PA" if state == "Pennsylvania"
replace state_code = "RI" if state == "Rhode Island"
replace state_code = "SC" if state == "South Carolina"
replace state_code = "SD" if state == "South Dakota"
replace state_code = "TN" if state == "Tennessee"
replace state_code = "TX" if state == "Texas"
replace state_code = "UT" if state == "Utah"
replace state_code = "VT" if state == "Vermont"
replace state_code = "VA" if state == "Virginia"
replace state_code = "WA" if state == "Washington"
replace state_code = "WV" if state == "West Virginia"
replace state_code = "WI" if state == "Wisconsin"
replace state_code = "WY" if state == "Wyoming"
replace state_code = "DC" if state == "Distr of Columbia"

* 4. clean up
keep year state_code govparty_c2 *_alt *_prop_all *_proof *_unified *cont*

recast str14 state_code	 

* 5. Merge datasets by state_code and year
merge 1:m state_code year using "${wd}/cps/state_props.dta"

* Check merge result
tab _merge
drop if _merge == 2   // keep only matches + master
drop _merge


* 6. Multiply selected variables by prop

foreach v of varlist *_alt *_prop_all *_proof *_unified *cont* *govparty_c2* {
        replace `v' = `v' * prop
}


* 7. Collapse (sum) to city_plus2–year level

* takes sums by year and city
ds *_prop_all *_proof *_unified *cont* *govparty_c2*, has(type numeric)
local vars `r(varlist)'

* 2) Build (mean) pairs: m_var = var
local meanpairs
foreach v of local vars {
    local meanpairs `meanpairs' (mean) m_`v'=`v'
}

* 3) Collapse sums + means in one go
collapse (sum) `vars' `meanpairs', by(city_plus2 year)

* 4) Set sums to missing when the mean is missing (i.e., group was all-missing)
foreach v of local vars {
    replace `v' = . if missing(m_`v')
}
drop m_*

*collapse (sum)  *_prop_all *_proof *_unified *cont* *govparty_c2*, by(city_plus2 year)

* Ensure year is numeric
destring year, replace force

* 8. Save 

save "${wd}/partisanship/city_partisanship2_yearly.dta", replace	 
